﻿/***********************************************************************************************************************************
 *    Name: DB_Get-Sizes.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE master;
IF OBJECT_ID(N'TempDB..#DBSize') IS NOT NULL DROP TABLE #DBSize;
CREATE TABLE #DBSize (Name NVARCHAR(255) NOT NULL, Size_MB INT NOT NULL);
DECLARE @tsql_command NVARCHAR(MAX)= N'';
SELECT @tsql_command+=  N'USE [' + database_name + N']; INSERT INTO #DBSize SELECT DB_NAME() AS Name, CAST(ROUND(SUM(size)/128.0,0) AS INT) AS Size_MB FROM sys.database_files WHERE type = 0;' FROM master.DBAdmin.DBList(N'@') dl;
EXECUTE (@tsql_command);
SELECT * FROM #DBSize ORDER BY Name;
GO